package cn.codewen.shop.dao.impl;

import cn.codewen.shop.dao.ProductDao;
import cn.codewen.shop.pojo.Product;
import cn.codewen.shop.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {
    @Override
    public List<Product> findAll() {

        //2. 声明一个List
        List<Product> products = new ArrayList<Product>();
        try {

            //1.1. 得到连接
            Connection conn = JDBCUtils.getConnection();
            //1.2. 创建stm对象
            Statement stm = conn.createStatement();


            //3. 定义SQL语句
            String sql = "select p.*,c.name category_name from product p,category c where p.category_id=c.id";
            //4. 执行sql语句
            ResultSet rs = stm.executeQuery(sql);
            //5. 遍历取出数据
            while (rs.next()) {
                //5.1 创建一个 category 对象
                Product product = new Product();
                //5.2 给category对象赋值
                product.setId(rs.getInt("id"));
                product.setName(rs.getString("name"));
                product.setLogo(rs.getString("logo"));
                product.setPrice(rs.getBigDecimal("price"));
                product.setSale(rs.getInt("sale"));
                product.setStock(rs.getInt("stock"));
                product.setCategoryId(rs.getInt("category_id"));
                product.setSort(rs.getInt("sort"));
                product.setStatus(rs.getInt("status"));
                product.setCategoryName(rs.getString("category_name"));
                //5.3 把category追加到categories List中
                products.add(product);
            }
            //6 关闭相关对象
            JDBCUtils.close(rs, stm, conn);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //7. 返回数据
        return products;
    }

    @Override
    public void insert(Product product) {

        try {

            //1.1. 得到连接
            Connection conn = JDBCUtils.getConnection();
            //1.2. 创建stm对象
            Statement stm = conn.createStatement();
            //1. 定义SQL语句
            String sql = "insert into product(name, logo, price, sale, stock, category_id, status, sort) values ('" + product.getName() + "','" + product.getLogo() + "'," + product.getPrice() + "," + product.getSale() + "," + product.getStock() + "," + product.getCategoryId() + "," + product.getStatus() + "," + product.getSort() + ")";
            //2. 执行SQL语句
            int count = stm.executeUpdate(sql);
            //3. 关闭对象
            JDBCUtils.close(null, stm, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public Product getOne(int id) {
        //0. 声明一个Category对象
        Product product = null;
        try {

            //1. 得到连接
            Connection conn = JDBCUtils.getConnection();
            //2. 定义SQL语句
            String sql = "select * from product where id=" + id;
            //3. 创建stm对象
            Statement stm = conn.createStatement();
            //4. 执行sql语句
            ResultSet rs = stm.executeQuery(sql);
            //5. 遍历取出数据
            if (rs.next()) {
                //5.1 创建一个 product 对象
                product = new Product();
                //5.2 给product对象赋值
                product.setId(rs.getInt("id"));
                product.setName(rs.getString("name"));
                product.setLogo(rs.getString("logo"));
                product.setPrice(rs.getBigDecimal("price"));
                product.setSale(rs.getInt("sale"));
                product.setStock(rs.getInt("stock"));
                product.setCategoryId(rs.getInt("category_id"));
                product.setStatus(rs.getInt("status"));
                product.setSort(rs.getInt("sort"));
            }
            //6 关闭相关对象
            JDBCUtils.close(rs, stm, conn);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //7. 返回数据
        return product;
    }

    @Override
    public void save(Product product) {

        try {
            //1. 得到连接
            Connection conn = JDBCUtils.getConnection();
            //2. 声明SQL语句
            String sql = "update product set name='" + product.getName() + "',logo='" + product.getLogo() + "',price=" + product.getPrice() + ",sale=" + product.getSale() + ",stock=" + product.getStock() + ",category_id=" + product.getCategoryId() + ",status=" + product.getStatus() + ",sort=" + product.getSort() + " where id =" + product.getId();
            //3. 创建stm对象

            Statement stm = conn.createStatement();

            //4. stm执行SQL
            stm.executeUpdate(sql);
            //5. 关闭
            JDBCUtils.close(null, stm, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    @Override
    public void del(int id) {
        try {
            //1. 得到连接
            Connection conn = JDBCUtils.getConnection();
            //2. 声明SQL语句
            String sql = "delete from product where id="+id;
            //3. 创建stm对象

            Statement stm = conn.createStatement();

            //4. stm执行SQL
            stm.executeUpdate(sql);
            //5. 关闭
            JDBCUtils.close(null, stm, conn);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
